﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;

/// <summary>
/// Summary description for ClassName
/// </summary>
public class CashCardManager
{
    public CashCardManager()
    {
        //
        // TODO: Add constructor logic here
        //
    }
    
    public static int NewCashCardApp(CustomerCashCard c){
        
        string sql = @"INSERT INTO CustomerCashCard(
            CashCardAppNo,
            CustomerId,
            ApplicationDate,
            CashCardStatus,
            CashCardStatusDate,
            DocumentStatus,
            CashCardNo,
            CashCardType,
            PartnerBank,
            NameOnCard,
            CardExpDate,
            CardDelStatus,
            DelStatusDate,
            CardDelCourier,
            DelDate,
            Remarks,
            DelRemarks,
            DateCreated,
            DateUpdated
            )
            VALUES(@0,@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17,@18)";
         List<object> param = new List<object>();
           
            param.Add(c.CashCardAppNo);
            param.Add(c.CustomerID);
            param.Add(UtilityManager.GetDefaultDateIfNull(c.ApplicationDate));
            param.Add(c.CashCardStatus);
            param.Add(UtilityManager.GetDefaultDateIfNull(c.CashCardStatusDate));
            param.Add(c.DocumentStatus);
            param.Add(c.CashCardNo);
            param.Add(c.CashCardType);
            param.Add(c.PartnerBank);
            param.Add(c.NameOnCard);
            param.Add(UtilityManager.GetDefaultDateIfNull(c.CardExpDate));
            param.Add(c.CardDelStatus);
            param.Add(UtilityManager.GetDefaultDateIfNull(c.DelStatusDate));
            param.Add(c.CardDelCourier);
            param.Add(UtilityManager.GetDefaultDateIfNull(c.DelDate));
            param.Add(c.Remarks);
            param.Add(c.DelRemarks);
            param.Add(c.DateCreated);
            param.Add(c.DateUpdated);
        
          var result = DBManager.Execute(sql,param.ToArray());
          return result;
          
    }
    
    public static int UpdateCustomerCC(dynamic c){
        string sql =string.Format(@"UPDATE CustomerCashCard 
                      SET  ApplicationDate='{0}',
                            CashCardStatus='{1}',
                            CashCardStatusDate='{2}',
                            DocumentStatus='{3}',
                            CashCardNo='{4}',
                            CashCardType='{5}',
                            PartnerBank='{6}',
                            StatusDate='{7}',
                            NameOnCard='{8}',
                            CardExpDate='{9}',
                            CardDelStatus='{10}',
                            DelStatusDate='{11}',
                            CardDelCourier='{12}',
                            DelDate='{13}',
                            Remarks='{14}',
                            DelRemarks='{15}',
                            DateUpdated='{16}'
                            WHERE CashCardAppNo='{17}' 
                            AND CustomerId='{18}'",UtilityManager.GetDefaultDateIfNull(c.ApplicationDate),c.CashCardStatus,UtilityManager.GetDefaultDateIfNull(c.CashCardStatusDate),c.DocumentStatus,c.CashCardNo,c.CashCardType,c.PartnerBank,UtilityManager.GetDefaultDateIfNull(c.StatusDate),c.NameOnCard,UtilityManager.GetDefaultDateIfNull(c.CardExpDate),c.CardDelStatus,UtilityManager.GetDefaultDateIfNull(c.DelStatusDate),c.CardDelCourier,UtilityManager.GetDefaultDateIfNull(c.DelDate),c.Remarks,c.DelRemarks,c.DateUpdated,c.CashCardAppNo,c.CustomerID);
          
          var result = DBManager.Execute(sql,null);
          return result;
        
    }
    
    public static int UpdateCustomer(CustomerCashCard c){
       string sql=@"UPDATE Customer SET
                    CustomerClassification=@0,
                    CustomerStatus=@1,
                    Country=@2,
                    Gender=@3,
                    Nationality=@4,
                    Bdate=@5,
                    PlaceOfBirth=@6,
                    IDNumber=@7,
                    IDType=@8,
                    SrcOfFunds=@9,
                    Employment=@10,
                    Employer=@11,
                    NatureOfWork=@12,
                    DateUpdated=@13
                    WHERE CustomerID=@14";
        
         List<object> param = new List<object>();
            param.Add(c.CustomerClassification);
            param.Add(c.CustomerStatus);
            param.Add(c.Country);
            param.Add(c.Gender);
            param.Add(c.Nationality);
            param.Add(UtilityManager.GetDefaultDateIfNull(c.Bdate));
            param.Add(c.PlaceOfBirth);
            param.Add(c.IDNumber);
            param.Add(c.IDType);
            param.Add(c.SrcOfFunds);
            param.Add(c.Employment);
            param.Add(c.Employer);
            param.Add(c.NatureOfWork);
            param.Add(c.DateUpdated);
            param.Add(c.CustomerID);
       return DBManager.Execute(sql,param.ToArray());  
    }
    
    public static IEnumerable<dynamic> ExtractCustomerData(Customer c,string type){
        
        
        string sql=@"SELECT CustomerID,
                        CustomerClassification,
                        CustomerStatus,
                        Country,
                        Gender,
                        Nationality,
                        Bdate,
                        PlaceOfBirth,
                        IDNumber,
                        IDType,
                        SrcOfFunds,
                        Employment,
                        Employer,
                        NatureOfWork
                    FROM Customer
                    WHERE CustomerID=@0";
        
        var query = DBManager.Query(sql,new object[]{c});
        return query;
    }
    
    public static IList<dynamic> ExtractSelected(string ccappnos){
        string[] ccs = ccappnos.Split(','); 
        StringBuilder sb = new StringBuilder();
        int ctr=0;
        foreach(string s in ccs){
            if(ctr<ccs.Length-1){
                sb.Append("'").Append(s).Append("',");
            }else{
                 sb.Append("'").Append(s).Append("'");
            }
            ctr++;
        }
        
         var sql = @"SELECT * FROM Customer c,
                   CustomerCashCard cc 
                   WHERE c.CustomerID = cc.CustomerID 
                   AND cc.CashCardAppNo IN ("+ sb.ToString() + ")";
        
        var query = DBManager.Query(sql,null);
        return query;
    }
    
     public static IList<dynamic> GetCashCardApp(string ccappno){
        
        
         var sql = string.Format(@"SELECT * FROM Customer c,
                   CustomerCashCard cc 
                   WHERE c.CustomerID = cc.CustomerID 
                   AND cc.CashCardAppNo='{0}'",ccappno);
        
        var query = DBManager.Query(sql,null);
        return query;
    }
    
    public static int UpdateDetailsForExtCC(string ccappnos,string filename,string action,string user){
        string[] ccs = ccappnos.Split(','); 
        StringBuilder sb = new StringBuilder();
        //get card status from AppConstants
        string cardstatus_ip = AppConstants.CASHCARD_INPROCESS;
        string cardstatus_ar = AppConstants.CASHCARD_ACT_REQUESTED;
        string delstatus_efp = AppConstants.CARD_DELIVERY_EFP;
        string delstatus_intran = AppConstants.CARD_DELIVERY_INTRAN;
         DateTime current = DateTime.Now;
        int ctr=0;
        foreach(string s in ccs){
            if(ctr<ccs.Length-1){
                sb.Append("'").Append(s).Append("',");
            }else{
                 sb.Append("'").Append(s).Append("'");
            }

            ctr++;
            
            //log changes
            CustomerCashCard cashCard = GetCashCardDetails(s);
            if(action.Equals("printing")){
                //update CashCardStatus,CardDelStatus,StatusDate,CashCardStatusDate,DelStatusDate
                if(String.Compare(cashCard.CashCardStatus,cardstatus_ip)!=0){
                    AuditManager.Log("CashCardStatus","PrintExtract",cashCard.CashCardStatus,cardstatus_ip,user,cashCard.CustomerID,cashCard.CashCardAppNo);
                }
                if(String.Compare(cashCard.CardDelStatus,delstatus_efp)!=0){
                   AuditManager.Log("CardDelStatus","PrintExtract",cashCard.CardDelStatus,delstatus_efp,user,cashCard.CustomerID,cashCard.CashCardAppNo);
                }
                /*if(cashCard.StatusDate!=null){
                  if(DateTime.Compare(Convert.ToDateTime(cashCard.StatusDate.ToString()),Convert.ToDateTime(current.ToString()))!=0){
                   AuditManager.Log("StatusDate","PrintExtract",cashCard.StatusDate.ToString(),current.ToString(),user,cashCard.CustomerID,cashCard.CashCardAppNo);
                  }  
                }else{
                   AuditManager.Log("StatusDate","PrintExtract",string.Empty,current.ToString(),user,cashCard.CustomerID,cashCard.CashCardAppNo);
           
                }*/
                
                if(cashCard.CashCardStatusDate!=null){
                  if(DateTime.Compare(Convert.ToDateTime(cashCard.CashCardStatusDate.ToString()),Convert.ToDateTime(current.ToString()))!=0){
                   AuditManager.Log("CashCardStatusDate","PrintExtract",cashCard.CashCardStatusDate.ToString(),current.ToString(),user,cashCard.CustomerID,cashCard.CashCardAppNo);
                  }      
                }else{
                   AuditManager.Log("CashCardStatusDate","PrintExtract",string.Empty,current.ToString(),user,cashCard.CustomerID,cashCard.CashCardAppNo);
           
                }

                if(cashCard.DelStatusDate!=null){
                  if(DateTime.Compare(Convert.ToDateTime(cashCard.DelStatusDate.ToString()),Convert.ToDateTime(current.ToString()))!=0){
                   AuditManager.Log("DelStatusDate","PrintExtract",cashCard.DelStatusDate.ToString(),current.ToString(),user,cashCard.CustomerID,cashCard.CashCardAppNo);
                  }
                }else{
                   AuditManager.Log("DelStatusDate","PrintExtract",string.Empty,current.ToString(),user,cashCard.CustomerID,cashCard.CashCardAppNo);
           
                }
                

            }else if(action.Equals("activation")){
                //update CashCardStatus,CashCardStatusDate,
                if(String.Compare(cashCard.CashCardStatus,cardstatus_ar)!=0){
                   AuditManager.Log("CashCardStatus","CardActReqExtract",cashCard.CashCardStatus,cardstatus_ar,user,cashCard.CustomerID,cashCard.CashCardAppNo);
                }
               if(cashCard.CashCardStatusDate!=null){
                  if(DateTime.Compare(Convert.ToDateTime(cashCard.CashCardStatusDate.ToString()),Convert.ToDateTime(current.ToString()))!=0){
                   AuditManager.Log("CashCardStatusDate","CardActReqExtract",cashCard.CashCardStatusDate.ToString(),current.ToString(),user,cashCard.CustomerID,cashCard.CashCardAppNo);
                  }      
                }else{
                   AuditManager.Log("CashCardStatusDate","CardActReqExtract",string.Empty,current.ToString(),user,cashCard.CustomerID,cashCard.CashCardAppNo);
           
                }
            }else if(action.Equals("delivery")){
                //update CashCardStatus,CardDelStatus,StatusDate,DelStatusDate
                if(String.Compare(cashCard.CashCardStatus,cardstatus_ip)!=0){
                    AuditManager.Log("CashCardStatus","DeliveryExtract",cashCard.CashCardStatus,cardstatus_ip,user,cashCard.CustomerID,cashCard.CashCardAppNo);
                }
                if(String.Compare(cashCard.CardDelStatus,delstatus_intran)!=0){
                   AuditManager.Log("CardDelStatus","DeliveryExtract",cashCard.CardDelStatus,delstatus_intran,user,cashCard.CustomerID,cashCard.CashCardAppNo);
                }

               /* if(cashCard.StatusDate!=null){
                  if(DateTime.Compare(Convert.ToDateTime(cashCard.StatusDate.ToString()),Convert.ToDateTime(current.ToString()))!=0){
                   AuditManager.Log("StatusDate","DeliveryExtract",cashCard.StatusDate.ToString(),current.ToString(),user,cashCard.CustomerID,cashCard.CashCardAppNo);
                  }  
                }else{
                   AuditManager.Log("StatusDate","DeliveryExtract",string.Empty,current.ToString(),user,cashCard.CustomerID,cashCard.CashCardAppNo);
           
                }*/
                if(cashCard.DelStatusDate!=null){
                 if(DateTime.Compare(Convert.ToDateTime(cashCard.DelStatusDate.ToString()),Convert.ToDateTime(current.ToString()))!=0){
                   AuditManager.Log("DelStatusDate","DeliveryExtract",cashCard.DelStatusDate.ToString(),current.ToString(),user,cashCard.CustomerID,cashCard.CashCardAppNo);
                 }
                }else{
                   AuditManager.Log("DelStatusDate","DeliveryExtract",string.Empty,current.ToString(),user,cashCard.CustomerID,cashCard.CashCardAppNo);
           
                }
                


            }
           
        }
        
     
        
        var sql=@"UPDATE CustomerCashCard ";
        StringBuilder param = new StringBuilder();
        param.Append(sql);
        
       
        if(action.Equals("printing")){
            param.Append("SET CashCardStatus='").Append(cardstatus_ip).Append("',CardDelStatus='").Append(delstatus_efp).Append("',StatusDate='"+current+"',CardPrintExtDate='"+current+"',CardPrintExtFile='"+filename+"',CashCardStatusDate='"+current+"',DelStatusDate='"+current+"'");
            
        }
        
        if(action.Equals("activation")){
            param.Append("SET CashCardStatus='").Append(cardstatus_ar).Append("',CashCardStatusDate='"+current+"',RAExtDate='"+current+"',RAExtFile='"+filename+"'");
        }
        
       if(action.Equals("delivery")){
            param.Append("SET CashCardStatus='").Append(cardstatus_ip).Append("',CardDelStatus='").Append(delstatus_intran).Append("',StatusDate='"+current+"',CourierExtDate='"+current+"',CourierExtFile='"+filename+"',DelStatusDate='"+current+"'");
        }
        
        //append the ccappnos
        param.Append(" WHERE CashCardAppNo IN ("+ sb.ToString() + ")");
        
       
        
        var query = DBManager.Execute(param.ToString(),null);
        return query;
        
    }
    
    public static IList<dynamic> ExtractData(Dictionary<string,string> c,string action){
        var sql = @"SELECT * FROM Customer c,
                   CustomerCashCard cc 
                   WHERE c.CustomerID = cc.CustomerID ";
      
        StringBuilder sb = new StringBuilder();
        sb.Append(sql);
        
         //get card status from AppConstants
        string cardstatus_validated = AppConstants.CASHCARD_VALIDATED;
        string cardstatus_ar = AppConstants.CASHCARD_ACT_REQUESTED;
        string cardstatus_far = AppConstants.CASHCARD_FOR_ACT_REQUEST;
        string delstatus_del = AppConstants.CARD_DELIVERY_DELIVERED;
        string delstatus_fd = AppConstants.CARD_DELIVERY_FD;
        
        //check action 
        if(action.Equals("printing")){
            sb.Append(" AND cc.CashCardStatus='").Append(cardstatus_validated).Append("'");
        }
        if(action.Equals("activation")){
            sb.Append(" AND cc.CashCardStatus='").Append(cardstatus_far).Append("' AND cc.CardDelStatus='").Append(delstatus_del).Append("' AND c.IDType!='' AND c.IDNumber!='' ");
        }
        if(action.Equals("delivery")){
            sb.Append(" AND cc.CardDelStatus='").Append(delstatus_fd).Append("'");
        }
        List<object> param = new List<object>();
        int p=0;
        //check which parameter is included and append it
         if(!(c["Solicitor"].Equals("0"))){
            sb.Append(string.Format(" AND c.Solicitor=@{0}",p++));
            param.Add(c["Solicitor"]);
        }
        
        if(!(c["CustomerClass"].Equals("0"))){
            sb.Append(string.Format(" AND c.CustomerClassification=@{0}",p++));
            param.Add(c["CustomerClass"]);
        }
        
        if(c["Region"]!="0"){
            sb.Append(string.Format(" AND c.Region=@{0}",p++));
            param.Add(c["Region"]);
        }
        
        if(c["Country"]!="0"){
            sb.Append(string.Format(" AND c.CurrMailCountry=@{0}",p++));
            param.Add(c["Country"]);
        }
        
        /*if(!(c["City"].Equals("0"))){
            sb.Append(string.Format(" AND c.CurrMailCity=@{0}",p++));
            param.Add(c["City"]);
        }*/
        
        if(c["State"]!="0"){
            sb.Append(string.Format(" AND c.CurrMailState=@{0}",p++));
            param.Add(c["State"]);
        }
        
        if(!(c["Pbank"].Equals("0"))){
            sb.Append(string.Format(" AND cc.PartnerBank=@{0}",p++));
            param.Add(c["Pbank"]);
        }

        var query = DBManager.Query(sb.ToString(),param.ToArray());
        return query;
        
    }
    
    public static CustomerCashCard GetCashCardDetails(string id){
        string sql = string.Format(@"SELECT CashCardAppNo,
                            CustomerID,
                            ApplicationDate,
                            CashCardStatus,
                            CashCardStatusDate,
                            DocumentStatus,
                            CashCardNo,
                            CashCardType,
                            PartnerBank,
                            StatusDate,
                            NameOnCard,
                            CardExpDate,
                            CardDelStatus,
                            DelStatusDate,
                            CardDelCourier,
                            DelDate,
                            Remarks,
                            DelRemarks,
                            DateUpdated
                            FROM CustomerCashCard WHERE CashCardAppNo='{0}'",id);
        var query = DBManager.Query(sql,null);
        if(query.Count>0){
            var ccold = query[0];
            CustomerCashCard cc = new CustomerCashCard();
            cc.CashCardAppNo=ccold.CashCardAppNo;
            cc.CustomerID = ccold.CustomerID;
            cc.ApplicationDate= ccold.ApplicationDate;
            cc.CashCardStatus=ccold.CashCardStatus;
            cc.CashCardStatusDate=ccold.CashCardStatusDate;
            cc.DocumentStatus=ccold.DocumentStatus;
            cc.CashCardNo=ccold.CashCardNo;
            cc.CashCardType=ccold.CashCardType;
            cc.PartnerBank=ccold.PartnerBank;
            cc.StatusDate=ccold.StatusDate;
            cc.NameOnCard=ccold.NameOnCard;
            cc.CardExpDate=ccold.CardExpDate;
            cc.CardDelStatus=ccold.CardDelStatus;
            cc.DelStatusDate=ccold.DelStatusDate;
            cc.CardDelCourier=ccold.CardDelCourier;
            cc.DelDate=ccold.DelDate;
            cc.Remarks=ccold.Remarks;
            cc.DelRemarks=ccold.DelRemarks;
            cc.DateUpdated=ccold.DateUpdated;
            return cc;
        }
        return new CustomerCashCard();    
        
        
    }
    
     public static List<Dictionary<string,string>> ExtFromCIFXLS(string path,string sh){
        try{
            ExcelManager.Template=path;
            HSSFWorkbook wb = ExcelManager.InitializeWB("MyRemit Cash Card");
            ISheet sheet = wb.GetSheet(sh);
            int dRow=2;
            int hRow=0;
            string[] colH = {"CashCardAppNo",
                "CashCardNo",
                "AccountNo",
                "LName",
                "FName",
                "Add1",
                "Add2",
                "City",
                "Country",
                "LandLineNo",
                "MobileNo",
                "Gender",
                "Bdate",
                "PlaceOfBirth",
                "LandLineNo",
                "IDType",
                "IDNo",
                "TIN",
                "SSS",
                "NatureOfWork",
                "OccupationCode",
                "Employer",
                "Nationality",
                "SourceOfFunds"
              };
            
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
            List<Dictionary<string,string>> ccList = new List<Dictionary<string,string>>();
            while (rows.MoveNext()){
                Dictionary<string,string> cc = new Dictionary<string,string>();
                IRow r=(HSSFRow)rows.Current;
                for(int i=0;i<colH.Length;i++){
                    ICell cell = r.GetCell(i);
                    if(cell!=null){
                        cc[colH[i]]=cell.ToString();
                   }else{
                       cc[colH[i]]="";
                    }
                }    
                 ccList.Add(cc);
            }
            return ccList;
            
        }catch(Exception e){
            throw e;
        }
    }
    
    
   public static List<Dictionary<string,string>> ExtFromDelXLS(string path,string sh){
        try{
            ExcelManager.Template=path;
            HSSFWorkbook wb = ExcelManager.InitializeWB("MyRemit Cash Card");
            ISheet sheet = wb.GetSheet(sh);
            int dRow=2;
            int hRow=0;
            string[] colH = {"CashCardAppNo",
                "Name",
                "Address",
                "City",
                "State",
                "Country",
                "Zip",
                "DelStatus",
                "DelStatusDate",
                "DelDate",
                "Remarks",
                "IDType",
                "IDNumber",
                "DocumentStatus"
              };
            
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
            List<Dictionary<string,string>> ccList = new List<Dictionary<string,string>>();
            while (rows.MoveNext()){
                Dictionary<string,string> cc = new Dictionary<string,string>();
                IRow r=(HSSFRow)rows.Current;
                for(int i=0;i<colH.Length;i++){
                    ICell cell = r.GetCell(i);
                    if(cell!=null){
                        cc[colH[i]]=cell.ToString();
                   }else{
                       cc[colH[i]]="";
                    }
                }    
                 ccList.Add(cc);
            }
            return ccList;
            
        }catch(Exception e){
            throw e;
        }
    }
    
    
    
    
    
   
    
   
   
    
    
    
    
    
    
}
